--    Program: 		CAPAREXT_CUSTOMER_ACCOUNT.sql
--    Description:	Extract AR Customer Account information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   l_set_of_books_id		number;
   l_business_group_id		number;
   
   cursor cur_account (p_business_group_id number) is
      select * from hz_cust_accounts hca
      where exists (select * 
                    from   chz_parties cp
                    where  cp.s_business_group_id = p_business_group_id
			        and    cp.party_id = hca.party_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_CUSTOMER_ACCOUNT.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books short name not found');
       --raise;
   end; 
   
   begin
     select business_group_id
     into   l_business_group_id
     from   hr_organization_units 
     where  upper(name) = 'BG_'||upper(substr(l_ngo_sob, -4));
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': business group not found');
       --raise;
   end;       

   for cur in cur_account(l_business_group_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file, 
       cur.CUST_ACCOUNT_ID                 || chr(9) ||
       cur.PARTY_ID                        || chr(9) ||
       cur.LAST_UPDATE_DATE                || chr(9) ||
       cur.ACCOUNT_NUMBER                  || chr(9) ||
       cur.LAST_UPDATED_BY                 || chr(9) ||
       cur.CREATION_DATE                   || chr(9) ||
       cur.CREATED_BY                      || chr(9) ||
       cur.LAST_UPDATE_LOGIN               || chr(9) ||
       cur.REQUEST_ID                      || chr(9) ||
       cur.PROGRAM_APPLICATION_ID          || chr(9) ||
       cur.PROGRAM_ID                      || chr(9) ||
       cur.PROGRAM_UPDATE_DATE             || chr(9) ||
       cur.WH_UPDATE_DATE                  || chr(9) ||
       cur.ATTRIBUTE_CATEGORY              || chr(9) ||
       cur.ATTRIBUTE1                      || chr(9) ||
       cur.ATTRIBUTE2                      || chr(9) ||
       cur.ATTRIBUTE3                      || chr(9) ||
       cur.ATTRIBUTE4                      || chr(9) ||
       cur.ATTRIBUTE5                      || chr(9) ||
       cur.ATTRIBUTE6                      || chr(9) ||
       cur.ATTRIBUTE7                      || chr(9) ||
       cur.ATTRIBUTE8                      || chr(9) ||
       cur.ATTRIBUTE9                      || chr(9) ||
       cur.ATTRIBUTE10                     || chr(9) ||
       cur.ATTRIBUTE11                     || chr(9) ||
       cur.ATTRIBUTE12                     || chr(9) ||
       cur.ATTRIBUTE13                     || chr(9) ||
       cur.ATTRIBUTE14                     || chr(9) ||
       cur.ATTRIBUTE15                     || chr(9) ||
       cur.ATTRIBUTE16                     || chr(9) ||
       cur.ATTRIBUTE17                     || chr(9) ||
       cur.ATTRIBUTE18                     || chr(9) ||
       cur.ATTRIBUTE19                     || chr(9) ||
       cur.ATTRIBUTE20                     || chr(9) ||
       cur.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
       cur.GLOBAL_ATTRIBUTE1               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE2               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE3               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE4               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE5               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE6               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE7               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE8               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE9               || chr(9) ||
       cur.GLOBAL_ATTRIBUTE10              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE11              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE12              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE13              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE14              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE15              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE16              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE17              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE18              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE19              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE20              || chr(9) ||
       cur.ORIG_SYSTEM_REFERENCE           || chr(9) ||
       cur.STATUS                          || chr(9) ||
       cur.CUSTOMER_TYPE                   || chr(9) ||
       cur.CUSTOMER_CLASS_CODE             || chr(9) ||
       cur.PRIMARY_SALESREP_ID             || chr(9) ||
       cur.SALES_CHANNEL_CODE              || chr(9) ||
       cur.ORDER_TYPE_ID                   || chr(9) ||
       cur.PRICE_LIST_ID                   || chr(9) ||
       cur.SUBCATEGORY_CODE                || chr(9) ||
       cur.TAX_CODE                        || chr(9) ||
       cur.FOB_POINT                       || chr(9) ||
       cur.FREIGHT_TERM                    || chr(9) ||
       cur.SHIP_PARTIAL                    || chr(9) ||
       cur.SHIP_VIA                        || chr(9) ||
       cur.WAREHOUSE_ID                    || chr(9) ||
       cur.PAYMENT_TERM_ID                 || chr(9) ||
       cur.TAX_HEADER_LEVEL_FLAG           || chr(9) ||
       cur.TAX_ROUNDING_RULE               || chr(9) ||
       cur.COTERMINATE_DAY_MONTH           || chr(9) ||
       cur.PRIMARY_SPECIALIST_ID           || chr(9) ||
       cur.SECONDARY_SPECIALIST_ID         || chr(9) ||
       cur.ACCOUNT_LIABLE_FLAG             || chr(9) ||
       cur.RESTRICTION_LIMIT_AMOUNT        || chr(9) ||
       cur.CURRENT_BALANCE                 || chr(9) ||
       cur.PASSWORD_TEXT                   || chr(9) ||
       cur.HIGH_PRIORITY_INDICATOR         || chr(9) ||
       cur.ACCOUNT_ESTABLISHED_DATE        || chr(9) ||
       cur.ACCOUNT_TERMINATION_DATE        || chr(9) ||
       cur.ACCOUNT_ACTIVATION_DATE         || chr(9) ||
       cur.CREDIT_CLASSIFICATION_CODE      || chr(9) ||
       cur.DEPARTMENT                      || chr(9) ||
       cur.MAJOR_ACCOUNT_NUMBER            || chr(9) ||
       cur.HOTWATCH_SERVICE_FLAG           || chr(9) ||
       cur.HOTWATCH_SVC_BAL_IND            || chr(9) ||
       cur.HELD_BILL_EXPIRATION_DATE       || chr(9) ||
       cur.HOLD_BILL_FLAG                  || chr(9) ||
       cur.HIGH_PRIORITY_REMARKS           || chr(9) ||
       cur.PO_EFFECTIVE_DATE               || chr(9) ||
       cur.PO_EXPIRATION_DATE              || chr(9) ||
       cur.REALTIME_RATE_FLAG              || chr(9) ||
       cur.SINGLE_USER_FLAG                || chr(9) ||
       cur.WATCH_ACCOUNT_FLAG              || chr(9) ||
       cur.WATCH_BALANCE_INDICATOR         || chr(9) ||
       cur.GEO_CODE                        || chr(9) ||
       cur.ACCT_LIFE_CYCLE_STATUS          || chr(9) ||
       cur.ACCOUNT_NAME                    || chr(9) ||
       cur.DEPOSIT_REFUND_METHOD           || chr(9) ||
       cur.DORMANT_ACCOUNT_FLAG            || chr(9) ||
       cur.NPA_NUMBER                      || chr(9) ||
       cur.PIN_NUMBER                      || chr(9) ||
       cur.SUSPENSION_DATE                 || chr(9) ||
       cur.WRITE_OFF_ADJUSTMENT_AMOUNT     || chr(9) ||
       cur.WRITE_OFF_PAYMENT_AMOUNT        || chr(9) ||
       cur.WRITE_OFF_AMOUNT                || chr(9) ||
       cur.SOURCE_CODE                     || chr(9) ||
       cur.COMPETITOR_TYPE                 || chr(9) ||
       cur.COMMENTS                        || chr(9) ||
       cur.DATES_NEGATIVE_TOLERANCE        || chr(9) ||
       cur.DATES_POSITIVE_TOLERANCE        || chr(9) ||
       cur.DATE_TYPE_PREFERENCE            || chr(9) ||
       cur.OVER_SHIPMENT_TOLERANCE         || chr(9) ||
       cur.UNDER_SHIPMENT_TOLERANCE        || chr(9) ||
       cur.OVER_RETURN_TOLERANCE           || chr(9) ||
       cur.UNDER_RETURN_TOLERANCE          || chr(9) ||
       cur.ITEM_CROSS_REF_PREF             || chr(9) ||
       cur.SHIP_SETS_INCLUDE_LINES_FLAG    || chr(9) ||
       cur.ARRIVALSETS_INCLUDE_LINES_FLAG  || chr(9) ||
       cur.SCHED_DATE_PUSH_FLAG            || chr(9) ||
       cur.INVOICE_QUANTITY_RULE           || chr(9) ||
       cur.PRICING_EVENT                   || chr(9) ||
       cur.ACCOUNT_REPLICATION_KEY         || chr(9) ||
       cur.STATUS_UPDATE_DATE              || chr(9) ||
       cur.AUTOPAY_FLAG                    || chr(9) ||
       cur.NOTIFY_FLAG                     || chr(9) ||
       cur.LAST_BATCH_ID                   || chr(9) ||
       cur.ORG_ID                          || chr(9) ||
       cur.OBJECT_VERSION_NUMBER           || chr(9) ||
       cur.CREATED_BY_MODULE               || chr(9) ||
       cur.APPLICATION_ID                  || chr(9) ||
       cur.SELLING_PARTY_ID
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Customer Account not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_CUSTOMER_ACCOUNT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
